package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Vector;

import modelo.Cereal;
import modelo.Lote.EstadoActividad;
import modelo.LoteSimple;
import modelo.LoteSimple.Estado;
import modelo.Mineral;
import modelo.MineralLote;
import modelo.Model;
import modelo.Propietario;

import org.jdesktop.observablecollections.ObservableCollections;
import org.jdesktop.observablecollections.ObservableList;

public class AdmPersistenciaLoteSimple{

	private static AdmPersistenciaLoteSimple instancia;

	public static AdmPersistenciaLoteSimple getinstancia (){
		if(instancia == null)
			instancia = new AdmPersistenciaLoteSimple();
		return instancia;	
	}

	public Vector<LoteSimple> selectAll(){
		try{
			Vector<LoteSimple> resultados = new Vector<LoteSimple>();
			Connection connection = PoolConnection.getPoolConnecion().getConnection();
			Statement s = connection.createStatement();
			ResultSet rs = s.executeQuery("SELECT nrolote,estado_actividad,nombre,estado,propietario,minerales,porc_minerales,areasiembra FROM lote_simple a INNER JOIN cereales c on a.ultimo_cereal_sembrado = c.codigo");
			while(rs.next()){
				int id = rs.getInt(1);
				EstadoActividad estadoA = EstadoActividad.valueOf(rs.getString(2));
				Cereal c = Model.getInstance().buscarCereal(rs.getString(3));
				Estado estado = Estado.valueOf(rs.getString(4));
				Propietario p = Model.getInstance().buscarPropietarioCodigo(rs.getInt(5));
				String[] mineralesN = rs.getString(6).split(",");
				String[] mineralesP = rs.getString(7).split(",");
				float area = rs.getFloat(8);
				ObservableList<MineralLote> vml = ObservableCollections.observableList(new ArrayList<MineralLote>());
				for(int i=0;i<mineralesN.length;i++){
					Mineral m = Mineral.valueOf(mineralesN[i]);
					MineralLote ml = new MineralLote(m,Integer.parseInt(mineralesP[i]));
					vml.add(ml);
				}

				LoteSimple ls = new LoteSimple(id,estadoA,c,estado,vml,p,area);
				resultados.add(ls);
			}
			PoolConnection.getPoolConnecion().releaseConnection(connection);
			return resultados;	
		}
		catch (Exception e){
			System.out.println(e.getMessage());
		}
		return null; 
	}

	public void insert(LoteSimple l){
		try{

			Connection connection = PoolConnection.getPoolConnecion().getConnection();
			PreparedStatement ps = connection.prepareStatement("INSERT INTO Lote_simple (nroLote,estado_actividad,ultimo_cereal_sembrado,estado,propietario,minerales,porc_minerales,areasiembra) values (?,?,?,?,?,?,?,?)");
			ps.setLong(1, l.getNroLote());
			ps.setString(2, l.getEstadoActividad().toString());
			ps.setInt(3,0);
			ps.setString(4, l.getEstado().toString());
			ps.setInt(5, l.getPropretario().getCodigo());
			ps.setString(6,l.getMineralesLote());
			ps.setString(7,l.getPorcentajesMineralesLote());
			ps.setFloat(8, l.getAreaSiembra());

			ps.execute();
			PoolConnection.getPoolConnecion().releaseConnection(connection);
		}
		catch (Exception e){
			System.out.println(e.getMessage());
		}
	}

	public void update(LoteSimple l){
		try{
			Connection connection = PoolConnection.getPoolConnecion().getConnection();
			PreparedStatement ps = connection.prepareStatement("UPDATE Lote_simple " 
					+"set estado_actividad = ?,"
					+"set ultimo_cereal_sembrado = ?."
					+"set propietario = ?,"
					+"set minereales = ?,"
					+"set porc_minerales = ?"
					+"set areasiembrea = ?"
					+"where nroLote = ?"
					);
			ps.setLong(8, l.getNroLote());
			ps.setString(1, l.getEstadoActividad().toString());
			ps.setInt(2,l.getUltimoCerealSembrado().getCodigo());
			ps.setString(3, l.getEstado().toString());
			ps.setInt(4, l.getPropretario().getCodigo());
			ps.setString(5,l.getMineralesLote());
			ps.setString(6,l.getPorcentajesMineralesLote());
			ps.setFloat(7, l.getAreaSiembra());
			ps.execute();
			PoolConnection.getPoolConnecion().releaseConnection(connection);
		}
		catch (Exception e){
			System.out.println(e.getMessage());
		}
	}

	public void delete(LoteSimple l){
		try{
			Connection connection = PoolConnection.getPoolConnecion().getConnection();
			PreparedStatement ps = connection.prepareStatement("DELETE FROM Lote_simple where nroLote = ?");
			ps.setLong(1, l.getNroLote());
			ps.execute();
			PoolConnection.getPoolConnecion().releaseConnection(connection);
		}
		catch (Exception e){
			System.out.println(e.getMessage());
		}
	}

	public void delete(int nroLote){
		try{
			Connection connection = PoolConnection.getPoolConnecion().getConnection();
			PreparedStatement ps = connection.prepareStatement("DELETE FROM Lote_simple where nroLote = ?");
			ps.setLong(1, nroLote);
			ps.execute();
			PoolConnection.getPoolConnecion().releaseConnection(connection);
		}
		catch (Exception e){
			System.out.println(e.getMessage());
		}
	}

	public LoteSimple buscarLoteSimple(int nroLote){
		try{
			LoteSimple l = null;
			Connection connection = PoolConnection.getPoolConnecion().getConnection();
			PreparedStatement ps = connection.prepareStatement("SELECT * FROM Lote_simple where nroLote = ?");
			ps.setLong(1, nroLote);
			ResultSet rs = ps.executeQuery();

			while(rs.next()){
				int id = rs.getInt(1);
				EstadoActividad estadoA = EstadoActividad.valueOf(rs.getString(2));
				Cereal c = null;
					if( rs.getInt(3)!=0)
						c = Model.getInstance().buscarCereal(rs.getString(3));

				Estado estado = Estado.valueOf(rs.getString(4));
				Propietario p = Model.getInstance().buscarPropietarioCodigo(rs.getInt(5));
				String[] mineralesN = rs.getString(6).split(",");
				String[] mineralesP = rs.getString(7).split(",");
				float area = rs.getFloat(8);
				ObservableList<MineralLote> vml = ObservableCollections.observableList(new ArrayList<MineralLote>());
				for(int i=0;i<mineralesN.length;i++){
					Mineral m = Mineral.valueOf(mineralesN[i]);
					MineralLote ml = new MineralLote(m,Integer.parseInt(mineralesP[i]));
					vml.add(ml);
				}

				l = new LoteSimple(id,estadoA,c,estado,vml,p,area); //ACA TIRA ESTE ERROR: java.util.Vector cannot be cast to org.jdesktop.observablecollections.ObservableList
			}
			PoolConnection.getPoolConnecion().releaseConnection(connection);
			return l;
		}
		catch (Exception e){
			System.out.println(e.getMessage());
		}
		return null;
	}

	public boolean existeLoteSimple(int nroLote) throws Exception{
		try{
			Connection connection = PoolConnection.getPoolConnecion().getConnection();
			PreparedStatement ps = connection.prepareStatement("SELECT * FROM Lote_Simple where nroLote = ?");
			ps.setLong(1, nroLote);
			ResultSet rs = ps.executeQuery();
			PoolConnection.getPoolConnecion().releaseConnection(connection);
			if(rs.getRow() == 0)
				return false;
			else
				return true;

		}
		catch (Exception e){
			System.out.println(e.getMessage());
			throw new Exception("No se pudo verificar");
		}

	}
}
